package com.jeesite.modules.dao;

import com.jeesite.common.dao.CrudDao;
import com.jeesite.common.mybatis.annotation.MyBatisDao;
import com.jeesite.modules.entity.DonationRecord;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 捐赠记录DAO接口
 * @author LaVenderGong
 * @version 2025-08-16
 */
@MyBatisDao
public interface DonationRecordDao extends CrudDao<DonationRecord> {

    /**
     * 原生更新捐赠记录状态（避免StackOverflowError）
     */
    @Update("UPDATE donationRecords SET payment_status = #{paymentStatus}, " +
            "transaction_id = #{transactionId}, donate_date = #{donateDate} " +
            "WHERE payment_order_id = #{paymentOrderId}")
    int updateDonationStatus(@Param("paymentOrderId") String paymentOrderId,
                             @Param("paymentStatus") String paymentStatus,
                             @Param("transactionId") String transactionId,
                             @Param("donateDate") java.util.Date donateDate);

    /**
     * 根据支付订单号查询捐赠记录
     */
    @Select("SELECT * FROM donationRecords WHERE payment_order_id = #{paymentOrderId}")
    DonationRecord findByPaymentOrderId(@Param("paymentOrderId") String paymentOrderId);

    /**
     * 根据交易号查询捐赠记录
     */
    @Select("SELECT * FROM donationRecords WHERE transaction_id = #{transactionId}")
    DonationRecord findByTransactionId(@Param("transactionId") String transactionId);

    /**
     * 获取捐赠统计信息
     */
    @Select("SELECT " +
            "COUNT(*) as totalCount, " +
            "COALESCE(SUM(CASE WHEN payment_status = 'PAID' THEN amount ELSE 0 END), 0) as totalAmount, " +
            "COALESCE(AVG(CASE WHEN payment_status = 'PAID' THEN amount END), 0) as avgAmount, " +
            "COUNT(CASE WHEN payment_status = 'PAID' THEN 1 END) as paidCount, " +
            "COUNT(DISTINCT CASE WHEN payment_status = 'PAID' THEN " +
            "COALESCE(NULLIF(game_nickname, ''), NULLIF(donor_name, ''), 'anonymous') END) as donorCount " +
            "FROM donationRecords")
    Map<String, Object> getDonationStatistics();

    /**
     * 获取捐赠排行榜
     */
    @Select("SELECT " +
            "COALESCE(game_nickname, donor_name, '匿名用户') as displayName, " +
            "game_nickname, " +
            "donor_name, " +
            "SUM(amount) as totalAmount, " +
            "COUNT(*) as donationCount " +
            "FROM donationRecords " +
            "WHERE payment_status = 'PAID' " +
            "GROUP BY COALESCE(game_nickname, donor_name, '匿名用户'), game_nickname, donor_name " +
            "ORDER BY totalAmount DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getDonationRanking(@Param("limit") int limit);

    /**
     * 获取最近的捐赠记录
     */
    @Select("SELECT * FROM donationRecords " +
            "WHERE payment_status = 'PAID' " +
            "ORDER BY donate_date DESC " +
            "LIMIT #{limit}")
    List<DonationRecord> getRecentDonations(@Param("limit") int limit);

    /**
     * 获取已支付捐赠记录的总数
     */
    @Select("SELECT COUNT(*) FROM donationRecords WHERE payment_status = 'PAID'")
    int getPaidDonationsCount();

    /**
     * 获取所有捐赠记录的总数
     */
    @Select("SELECT COUNT(*) FROM donationRecords")
    int getAllDonationsCount();

    /**
     * 获取所有捐赠记录
     */
    @Select("SELECT * FROM donationRecords ORDER BY donate_date DESC")
    List<DonationRecord> getAllDonations();

    /**
     * 分页获取已支付的捐赠记录
     */
    @Select("SELECT * FROM donationRecords " +
            "WHERE payment_status = 'PAID' " +
            "ORDER BY donate_date DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<DonationRecord> getPaidDonationsWithPaging(@Param("limit") int limit, @Param("offset") int offset);

    /**
     * 根据游戏账号ID查询捐赠记录
     */
    @Select("SELECT * FROM donationRecords WHERE game_account_id = #{gameAccountId}")
    List<DonationRecord> findByGameAccountId(@Param("gameAccountId") String gameAccountId);

    /**
     * 根据游戏昵称查询捐赠记录
     */
    @Select("SELECT * FROM donationRecords WHERE game_nickname = #{gameNickname}")
    List<DonationRecord> findByGameNickname(@Param("gameNickname") String gameNickname);

    /**
     * 根据支付状态统计数量
     */
    @Select("SELECT payment_status, COUNT(*) as count " +
            "FROM donationRecords " +
            "GROUP BY payment_status")
    List<Map<String, Object>> getStatusStatistics();

    /**
     * 获取指定时间范围内的捐赠记录
     */
    @Select("SELECT * FROM donationRecords " +
            "WHERE donate_date >= #{startDate} AND donate_date <= #{endDate} " +
            "ORDER BY donate_date DESC")
    List<DonationRecord> findByDateRange(@Param("startDate") String startDate, 
                                       @Param("endDate") String endDate);

    /**
     * 根据支付方式统计
     */
    @Select("SELECT payment_method, COUNT(*) as count, SUM(amount) as totalAmount " +
            "FROM donationRecords " +
            "WHERE payment_status = 'PAID' " +
            "GROUP BY payment_method")
    List<Map<String, Object>> getPaymentMethodStatistics();

    /**
     * 获取月度捐赠统计
     */
    @Select("SELECT " +
            "DATE_FORMAT(donate_date, '%Y-%m') as month, " +
            "COUNT(*) as count, " +
            "SUM(amount) as totalAmount " +
            "FROM donationRecords " +
            "WHERE payment_status = 'PAID' " +
            "GROUP BY DATE_FORMAT(donate_date, '%Y-%m') " +
            "ORDER BY month DESC " +
            "LIMIT 12")
    List<Map<String, Object>> getMonthlyStatistics();

    /**
     * 更新支付状态
     */
    @Update("UPDATE donationRecords SET " +
            "payment_status = #{status}, " +
            "transaction_id = #{transactionId}, " +
            "donate_date = NOW() " +
            "WHERE id = #{id}")
    int updatePaymentStatus(@Param("id") Long id,
                          @Param("status") String status,
                          @Param("transactionId") String transactionId);

    /**
     * 原生插入捐赠记录（避免StackOverflowError）
     */
    @org.apache.ibatis.annotations.Insert("INSERT INTO donationRecords (payment_order_id, amount, donor_name, message, payment_status, donate_date, game_account_id, game_nickname, contact_info) " +
            "VALUES (#{paymentOrderId}, #{amount}, #{donorName}, #{message}, #{paymentStatus}, #{donateDate}, #{gameAccountId}, #{gameNickname}, #{contactInfo})")
    int insertDonationRecord(@Param("paymentOrderId") String paymentOrderId,
                            @Param("amount") java.math.BigDecimal amount,
                            @Param("donorName") String donorName,
                            @Param("message") String message,
                            @Param("paymentStatus") String paymentStatus,
                            @Param("donateDate") java.util.Date donateDate,
                            @Param("gameAccountId") String gameAccountId,
                            @Param("gameNickname") String gameNickname,
                            @Param("contactInfo") String contactInfo);
}
